package com.es.hr.shopscore;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.common.CommonBean;
import com.es.util.db.Dao;
import com.es.util.db.DaoUtil;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class ShopScoreBiz {
	
	public boolean insertShopScore(String shopId, String shopScore, String startTime, String endTime, CommonBean tufa)
	throws Exception{
		boolean result = false;
		Dao dao = new Dao();
		String sql = "insert into hrm_shop_score (shop_id, total_score, start_time, end_time, tufashijian_code, tufashijian_value) values(?,?,?,?,?,?)";
		String sql2 = "insert into hrm_shop_score (shop_id, total_score, start_time, end_time, tufashijian_value) values(?,?,?,?,?)";

		try{	
			ArrayList<String> query = new ArrayList<String>();
			query.add(shopId);
			query.add(shopScore);
			query.add(startTime);
			query.add(endTime);
			if(!tufa.getCommonCode().equals(""))
				query.add(tufa.getCommonCode());
			query.add(tufa.getCommonValue());
			if(!tufa.getCommonCode().equals(""))
				dao.update(sql, query );
			else dao.update(sql2, query );
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public boolean updateShopScoreById(String id, String shopId, String shopScore, String startTime, String endTime, CommonBean tufa)
	throws Exception{
		boolean result = false;
		Dao dao = new Dao();
		String sql = "update hrm_shop_score set shop_id = ?, total_score = ?, start_time = ?, end_time = ?, tufashijian_code = ?, tufashijian_value = ? where shop_score_id = ?";
		String sql2 = "update hrm_shop_score set shop_id = ?, total_score = ?, start_time = ?, end_time = ?,  tufashijian_code= '', tufashijian_value = ? where shop_score_id = ?";

		try{	
			ArrayList<String> query = new ArrayList<String>();
			query.add(shopId);
			query.add(shopScore);
			query.add(startTime);
			query.add(endTime);
			if(!tufa.getCommonCode().equals(""))
				query.add(tufa.getCommonCode());
			query.add(tufa.getCommonValue());
			query.add(id);
			if(!tufa.getCommonCode().equals(""))
				dao.update(sql, query );
			else dao.update(sql2, query );
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public PageBean searchShopScoreByTime(String startTime,String endTime,int curpage)
	throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = " select shop_score_id, shop_id, start_time, end_time, tufashijian_code, tufashijian_value, total_score from hrm_shop_score ";
		String[] sqlp = {" start_time >= ? ", " end_time <= ? "};
		String and = " and ";
		String where = " where ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam2.add(startTime);
		sqlParam2.add(endTime);
		boolean flag = false;
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
				if(flag){
					sql += and + sqlp[i];
					sqlParam.add(param);
				}else{
					sql += where + sqlp[i];
					sqlParam.add(param);
					flag = true;
				}
			}

			}
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);

			pb.setParam(sqlParam2);
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public PageBean searchShopScoreWithShop(String startTime,String endTime, ArrayList<String> query,int curpage)
	throws Exception{
		Dao dao = new Dao();
		PageBean pb = null;
		String sql = " select score.shop_score_id, score.shop_id, score.start_time, score.end_time, score.tufashijian_code, score.tufashijian_value, score.total_score, shop.shop_code, shop.shop_name, shop.shop_area_value, shop.shop_province_value, shop.shop_city_value from hrm_shop_score score, hrm_shop_info shop where shop.shop_id = score.shop_id ";
		String[] sqlp = {" score.start_time >= ? ", " score.end_time <= ? "};
		String[] sqlq = {" shop.shop_code like '%'+UPPER(?)+'%' ", " shop.shop_name like '%'+UPPER(?)+'%' ", "shop.shop_area_code = ?", "shop.shop_province_code = ?", "shop.shop_city_code = ?"};
		String and = " and ";
		ArrayList<String> sqlParam = new ArrayList<String>();
		ArrayList<String> sqlParam2 = new ArrayList<String>();
		sqlParam2.add(startTime);
		sqlParam2.add(endTime);
		for(int i = 0; i < sqlParam2.size(); ++i){
			String param = sqlParam2.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlp[i];
					sqlParam.add(param);
			}
		}
		for(int i = 0; i < query.size(); ++i){
			String param = query.get(i);
			if(param != null && param.length() > 0){
					sql += and + sqlq[i];
					sqlParam.add(param);
			}
		}
		try{
			PageNavigator pageNav = new PageNavigator();
			int totalC = dao.getAllRow(sql, sqlParam);
			pageNav.setRowTotal(totalC);
			pageNav.setCurpage(curpage);
			if(totalC % pageNav.getPageRow() == 0){
				pageNav.setPageTotal(totalC / pageNav.getPageRow());
			}else{
				pageNav.setPageTotal(totalC / pageNav.getPageRow() + 1);
			}
			pb = dao.queryPageList(sql, pageNav, sqlParam);

			pb.setParam(sqlParam2);
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return pb;
	}
	
	public boolean deleteShopScoreById(String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "delete from hrm_shop_score where shop_score_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ShopScoreBean getShopScoreById(String id) throws Exception{
		Dao dao = new Dao();
		ShopScoreBean result= new ShopScoreBean();
		String sql = "select  shop_id, start_time, end_time, tufashijian_code, tufashijian_value, total_score from hrm_shop_score where shop_score_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			ResultSet rs = dao.Select(sql, sqlParam);
			while(rs.next()){
				result.setShopScoreId(id);
				result.setShopId(rs.getString(1));
				result.setStartTime(rs.getString(2));
				result.setEndTime(rs.getString(3));
				result.setTufashijianCode(rs.getString(4));
				result.setTufashijianValue(rs.getString(5));
				result.setTotalScore(rs.getString(6));
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ArrayList<HashMap<String,String>> exceptExcelShopScore(ArrayList<String> param)
	throws Exception {
	Dao dao = new Dao();
	ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
	ArrayList<String> query = new ArrayList<String>();

	try {
		String sql = "select *," +
				" (select top 1 common_value from hrm_common where common_code = shop.shop_jingxiaoshang_type and common_type='jingxiaoshangtype') shop_jingxiaoshang_type, " +
				" (select top 1 city_level from hrm_city where city_code = shop.shop_city_code) city_level "+
				"from hrm_shop_score score, hrm_shop_info shop"
		+" where score.shop_id = shop.shop_id ";
		String and = " and ";
		String[] sqlArrary = new String[] { " shop.shop_area_code=? ",
				" shop.shop_province_code=? ", " shop.shop_city_code=? ",
				" UPPER(shop.shop_code) like '%'+UPPER(?)+'%'",
				" UPPER(shop.shop_name) like '%'+UPPER(?)+'%'",
				" score.start_time >= ? ",
				" score.end_time <= ? "
				};
		for (int i = 0; i < param.size(); ++i) {
			if (!param.get(i).equals("")) {
					sql += and + sqlArrary[i];
					query.add(param.get(i).trim());
			}
		}
		ResultSet resultSet = null;
		if(query.size()==0)
			resultSet = dao.Select(sql);
		else
			resultSet = dao.Select(sql,query);
		ResultSetMetaData rss = resultSet.getMetaData();
		
		while(resultSet.next()){
				HashMap<String,String> map = new HashMap<String,String>();
				for(int i = 0;i<rss.getColumnCount(); i++){ 	
					if(!rss.getColumnName(i+1).equals("shop_id") && !rss.getColumnName(i+1).equals("shop_score_id")){
						map.put(rss.getColumnName(i+1), (String) resultSet.getObject(rss.getColumnName(i+1)));
					}
			 }	
			  list.add(map);
		  }
	} finally {
		dao.close();
	}
	return list;
	}
	
}
